package br.com.locautowebbd.endereco;

import java.sql.*;

import br.com.locautowebbd.util.JDBCConnectionUtil;
import br.com.locautowebbd.util.RepositorioException;

public class RepositorioEnderecoJDBC implements RepositorioEnderecos {

	public void atualizar(Endereco en) throws EnderecoInexistenteException,
			RepositorioException {
		Statement stmt = null;
		try {
			String query = "UPDATE TB_ENDERECO set " + " cep='" + en.getCep()
					+ "' " + "WHERE cep='" + en.getCep() + "'";
			stmt = JDBCConnectionUtil.obterConexao()
					.createStatement();
			int i = stmt.executeUpdate(query);
			if (i == 0) {
				throw new EnderecoInexistenteException(000);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RepositorioException(ex);
		} finally {
			try {
				stmt.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				throw new RepositorioException(ex);
			}
		}
	}

	public void inserir(Endereco en) throws RepositorioException{
		Statement stmt = null;
		try {
			String query = "INSERT INTO TB_ENDERECO VALUES( " + "'" + en.getLogradouro()
					+ "', " + "'" + en.getNumero() + "'," + en.getCep() + ", " + "'" + en.getBairro() + "" +
							", " + "'" + en.getCidade() + ", " + "'" + en.getUf() + ")"; 
			stmt = JDBCConnectionUtil.obterConexao().createStatement();
			stmt.executeUpdate(query);
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RepositorioException(ex);
		} finally {
			try {
				stmt.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				throw new RepositorioException(ex);
			}
		}
	}

	public Endereco procurar(int cep) throws EnderecoInexistenteException,
			RepositorioException {
		Statement stmt = null;
		ResultSet rs = null;
		Endereco procurado = null;
		try {
			String query = "SELECT * FROM TB_ENDERECO " + "WHERE cpe='" + cep+ "'";
			stmt = JDBCConnectionUtil.obterConexao()
					.createStatement();
			rs = stmt.executeQuery(query);
			if (rs.next()) {

				cep = rs.getInt(cep);
				int numero = rs.getInt("numero");
				String logradouro = rs.getString("logradouro");
				String bairro = rs.getString("bairro");
				String cidade = rs.getString("cidade");
				String uf = rs.getString("uf");


				procurado = new Endereco(logradouro,numero, cep, bairro,cidade,uf);

			} else {
				throw new EnderecoInexistenteException(cep);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RepositorioException(ex);
		} finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				throw new RepositorioException(ex);
			}
		}

		return procurado;

	}

	public void remover(int cep) throws EnderecoInexistenteException,
			RepositorioException {
		Statement stmt = null;
		try {
			String query = "DELETE FROM TB_ENDERECO " + "WHERE cep='" + cep + "'";
			stmt = JDBCConnectionUtil.obterConexao()
					.createStatement();
			int i = stmt.executeUpdate(query);
			if (i == 0) {
				throw new EnderecoInexistenteException(0);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RepositorioException(ex);
		} finally {
			try {
				stmt.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				throw new RepositorioException(ex);
			}
		}
	}

	public boolean existe(int cep) throws RepositorioException {
		Statement stmt = null;
		ResultSet rs = null;
		boolean resp = false;
		try {
			String query = "SELECT * FROM TB_VEICULO " + "WHERE cep='" + cep + "'";
			stmt = JDBCConnectionUtil.obterConexao()
					.createStatement();
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				resp = true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RepositorioException(ex);
		} finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				throw new RepositorioException(ex);
			}
		}

		return resp;
	}
}
